package org.black.phoneUtility.database.sqlite;

import org.black.phoneUtility.provider.PhoneUtilityContract;
import org.black.phoneUtility.sms.reply.provider.SmsReplyContract;
import org.black.phoneUtility.sms.schedule.provider.SmsScheduleContract;

import android.content.ContentValues;
import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class PhoneUtilitySQLiteOpenHelper extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 4;
	public static final String DATABASE_NAME = "PhoneUtility.sqlite";
	private static final String LOG_TAG = PhoneUtilitySQLiteOpenHelper.class
			.getName();

	public interface Tables {
		public static final String GLOBAL_SETTINGS = "Phone_Utility_Global_Settings";

		interface SMS {
			interface Reply {
				public static final String GLOBAL_SETTINGS = "SMSReply_Global_Settings";
				public static final String REPLIES = "SMS_Reply_SMSReplies";
				public static final String RESPONSE_NUMBER_FROM = "SM_SReply_ResponseNumberFrom";
				public static final String NONE_REPLY_NUMBER = "SMS_Reply_NoneReplyNumber";
				public static final String REPLY_TO_SPECIFIC_NUMBER = "SMS_Reply_ReplyToSpecificNumber";
				public static final String REPLY_TIMES_MODE = "SMS_Reply_ReplyTimesMode";
				public static final String ACTIVATION_RECORDS = "SMS_Reply_ActivationRecords";
				public static final String REPLY_HISTORIES = "SMS_Reply_ReplyHistories";
				public static final String APP_WIDGET_IDS = "SMS_Reply_SmsReplyAppWidgetIds";
			}

			interface Schedule {
				public static final String GLOBAL_SETTINGS = "SMS_Schedule_Global_Settings";
				public static final String SCHEDULES = "SMS_Schedules";
				public static final String RECIPIENTS = "SMS_Schedule_Recipients";
				public static final String SENDING_RECORDS = "SMS_Schedule_Sending_Records";
				public static final String SENDING_RECORD_RECIPIENTS = "SMS_Schedule_Sending_Record_Recipients";
			}
		}
	}

	private static final String PHONE_UTILITY_GLOBAL_SETTINGS_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.GLOBAL_SETTINGS
			+ "("
			+ PhoneUtilityContract.PhoneUtilityGlobalSettings._ID
			+ " INTEGER PRIMAR KEY" + ")";

	private static final String SMS_REPLY_GLOBAL_SETTING_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.GLOBAL_SETTINGS
			+ "("
			+ SmsReplyContract.SmsReplyGlobalSettings._ID
			+ " INTEGER PRIMARY KEY, "
			+ SmsReplyContract.SmsReplyGlobalSettings.ENABLE_AVOID_EXCESSIVE_REPLY
			+ " INTEGER NOT NULL DEFAULT 1, "
			+ SmsReplyContract.SmsReplyGlobalSettings.SHOULD_NOTIFICATION_DISPLAY_IN_NOTIFICATION_BAR
			+ " INTEGER NOT NULL DEFAULT 1, "
			+ SmsReplyContract.SmsReplyGlobalSettings.TURN_ON_SILENT_WHEN_REPLY_IS_ON
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.SmsReplyGlobalSettings.DO_NOT_SEND_REPLY_MESSAGE_ON_ROAMING
			+ " INTEGER NOT NULL DEFAULT 0 " + ")";

	private static final String SMS_REPLY_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.REPLIES
			+ "("
			+ SmsReplyContract.SmsReplies._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsReplyContract.SmsReplies.TITLE
			+ " TEXT NOT NULL, "
			+ SmsReplyContract.SmsReplies.SHOULD_RESPONSE_FROM_PHONE
			+ " INTEGER NOT NULL DEFAULT 1, "
			+ SmsReplyContract.SmsReplies.SHOULD_RESPONSE_FROM_SMS
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.SmsReplies.PHONE_MESSAGE
			+ " TEXT, "
			+ SmsReplyContract.SmsReplies.PHONE_SMS_USE_DIFFERENT_MESSAGE
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.SmsReplies.IS_ACTIVATED
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.SmsReplies.ACTIVATION_DATE_TIME
			+ " INTEGER, "
			+ SmsReplyContract.SmsReplies.REPLY_MODE
			+ " INTEGER NOT NULL DEFAULT "
			+ SmsReplyContract.SmsReplies.REPLY_MODE_VALUE_REPLY_DIRECTLY
			+ ", "
			+ SmsReplyContract.SmsReplies.SMS_MESSAGE
			+ " TEXT, "
			+ SmsReplyContract.SmsReplies.ENABLE_AVOID_EXCESSIVE_REPLY
			+ " INTEGER NOT NULL DEFAULT 1, "
			+ SmsReplyContract.SmsReplies.ENABLE_NOT_RESPONSE_TO_READ_MESSAGE
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.SmsReplies.DELAY_TIME_FOR_NOT_RESPONSE_TO_READ_MESSAGE
			+ " INTEGER, "
			+ SmsReplyContract.SmsReplies.ENABLE_NOT_RESPONSE_TO_RESPONSE_CALL
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.SmsReplies.DELAY_TIME_FOR_NOT_RESPONSE_TO_RESPONSE_CALL
			+ " INTEGER, "
			+ SmsReplyContract.SmsReplies.ENABLE_RINGER_MODE_SILENT
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.SmsReplies.CREATEION_DATE_TIME
			+ " INTEGER NOT NULL , "
			+ SmsReplyContract.SmsReplies.MODIFICATION_DATE_TIME
			+ " INTEGER NOT NULL,  "
			+ SmsReplyContract.SmsReplies.NOTIFY_AFTER_SENDING_MESSAGE
			+ " INTEGER NOT NULL DEFAULT 1, "
			+ SmsReplyContract.SmsReplies.REPLY_DATE_TIME_TYPE + " INTEGER, "
			+ SmsReplyContract.SmsReplies.EXPIRATED_AFTER_MILLISECONDS
			+ " INTEGER, " + SmsReplyContract.SmsReplies.PERIOD_START_DATE_TIME
			+ " INTEGER, " + SmsReplyContract.SmsReplies.PERIOD_END_DATE_TIME
			+ " INTEGER, " + SmsReplyContract.SmsReplies.REPEAT_START_TIME
			+ " INTEGER, " + SmsReplyContract.SmsReplies.REPEAT_END_TIME
			+ " INTEGER, " + SmsReplyContract.SmsReplies.IS_SUNDAY_ENABLE
			+ " INTEGER, " + SmsReplyContract.SmsReplies.IS_MONDAY_ENABLE
			+ " INTEGER, " + SmsReplyContract.SmsReplies.IS_TUESDAY_ENABLE
			+ " INTEGER, " + SmsReplyContract.SmsReplies.IS_WEDNESDAY_ENABLE
			+ " INTEGER, " + SmsReplyContract.SmsReplies.IS_THURSDAY_ENABLE
			+ " INTEGER, " + SmsReplyContract.SmsReplies.IS_FRIDAY_ENABLE
			+ " INTEGER, " + SmsReplyContract.SmsReplies.IS_SATURDAY_ENABLE
			+ " INTEGER " + ")";

	private static final String RESPONSE_NUMBER_FROM_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.RESPONSE_NUMBER_FROM
			+ "("
			+ SmsReplyContract.ResponseNumberFrom._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsReplyContract.ResponseNumberFrom.CREATION_DATE_TIME
			+ " INTEGER NOT NULL , "
			+ SmsReplyContract.ResponseNumberFrom.REFERENCE_SMS_REPLY_ID
			+ " INTEGER, "
			+ SmsReplyContract.ResponseNumberFrom.TYPE
			+ " INTEGER NOT NULL,"
			+ SmsReplyContract.ResponseNumberFrom.NAME
			+ " TEXT, "
			+ SmsReplyContract.ResponseNumberFrom.PHONE_NUMBER
			+ " TEXT, "
			+ SmsReplyContract.ResponseNumberFrom.SMS_KEYWORD
			+ " TEXT,"
			+ SmsReplyContract.ResponseNumberFrom.GROUP_NAME
			+ " TEXT, "
			+ SmsReplyContract.ResponseNumberFrom.NUMBERS_BEGIN_WITH
			+ " TEXT, "
			+ "FOREIGN KEY ("
			+ SmsReplyContract.ResponseNumberFrom.REFERENCE_SMS_REPLY_ID
			+ ") REFERENCES "
			+ Tables.SMS.Reply.REPLIES
			+ "("
			+ SmsReplyContract.SmsReplies._ID + ") ON DELETE CASCADE" + ")";

	private static final String REPLY_TO_SPECIFIC_NUMBER_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.REPLY_TO_SPECIFIC_NUMBER
			+ "("
			+ SmsReplyContract.ReplyToSpecificNumbers._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsReplyContract.ReplyToSpecificNumbers.CREATION_DATE_TIME
			+ " INTEGER NOT NULL , "
			+ SmsReplyContract.ReplyToSpecificNumbers.TYPE
			+ " INTEGER NOT NULL,"
			+ SmsReplyContract.ReplyToSpecificNumbers.NAME
			+ " TEXT, "
			+ SmsReplyContract.ReplyToSpecificNumbers.PHONE_NUMBER
			+ " TEXT, "
			+ SmsReplyContract.ReplyToSpecificNumbers.GROUP_NAME
			+ " TEXT, "
			+ SmsReplyContract.ReplyToSpecificNumbers.REFERENCE_SMS_REPLY_ID
			+ " INTEGER, "
			+ "FOREIGN KEY ("
			+ SmsReplyContract.ReplyToSpecificNumbers.REFERENCE_SMS_REPLY_ID
			+ ") REFERENCES "
			+ Tables.SMS.Reply.REPLIES
			+ "("
			+ SmsReplyContract.SmsReplies._ID + ") ON DELETE CASCADE" + ")";

	private static final String NONE_REPLY_NUMBER_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.NONE_REPLY_NUMBER
			+ "("
			+ SmsReplyContract.NoneReplyNumbers._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsReplyContract.NoneReplyNumbers.CREATION_DATE_TIME
			+ " INTEGER NOT NULL , "
			+ SmsReplyContract.NoneReplyNumbers.TYPE
			+ " INTEGER NOT NULL,"
			+ SmsReplyContract.NoneReplyNumbers.NAME
			+ " TEXT, "
			+ SmsReplyContract.NoneReplyNumbers.PHONE_NUMBER
			+ " TEXT, "
			+ SmsReplyContract.NoneReplyNumbers.SMS_KEYWORD
			+ " TEXT,"
			+ SmsReplyContract.NoneReplyNumbers.GROUP_NAME
			+ " TEXT, "
			+ SmsReplyContract.NoneReplyNumbers.NUMBERS_BEGIN_WITH
			+ " TEXT, "
			+ SmsReplyContract.NoneReplyNumbers.REFERENCE_SMS_REPLY_ID
			+ " INTEGER, "
			+ "FOREIGN KEY ("
			+ SmsReplyContract.NoneReplyNumbers.REFERENCE_SMS_REPLY_ID
			+ ") REFERENCES "
			+ Tables.SMS.Reply.REPLIES
			+ "("
			+ SmsReplyContract.SmsReplies._ID + ") ON DELETE CASCADE" + ")";
	private static final String REPLY_TIMES_MODE_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.REPLY_TIMES_MODE
			+ "("
			+ SmsReplyContract.ReplyTimesModes._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsReplyContract.ReplyTimesModes.TYPE
			+ " INTEGER NOT NULL, "
			+ SmsReplyContract.ReplyTimesModes.TIMES
			+ " INTEGER , "
			+ SmsReplyContract.ReplyTimesModes.IS_ACTIVATED
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.ReplyTimesModes.ACTIVATION_DATE_TIME
			+ " INTEGER , "
			+ SmsReplyContract.ReplyTimesModes.REFERENCE_SMS_REPLY_ID
			+ " INTEGER, "
			+ "FOREIGN KEY ("
			+ SmsReplyContract.ReplyTimesModes.REFERENCE_SMS_REPLY_ID
			+ ") REFERENCES "
			+ Tables.SMS.Reply.REPLIES
			+ "("
			+ SmsReplyContract.SmsReplies._ID + ") ON DELETE CASCADE" + ")";
	private static final String ACTIVATION_RECORD_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.ACTIVATION_RECORDS
			+ "("
			+ SmsReplyContract.ActivationRecords._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsReplyContract.ActivationRecords.CREATION_DATE_TIME
			+ " INTEGER NOT NULL , "
			+ SmsReplyContract.ActivationRecords.IS_ACTIVATED
			+ " INTEGER NOT NULL DEFAULT 0, "
			+ SmsReplyContract.ActivationRecords.REFERENCE_SMS_REPLY_ID
			+ " INTEGER, "
			+ "FOREIGN KEY ("
			+ SmsReplyContract.ActivationRecords.REFERENCE_SMS_REPLY_ID
			+ ") REFERENCES "
			+ Tables.SMS.Reply.REPLIES
			+ "("
			+ SmsReplyContract.SmsReplies._ID + ") ON DELETE SET NULL" + ")";
	private static final String REPLY_HISTORY_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.REPLY_HISTORIES
			+ "("
			+ SmsReplyContract.ReplyHistories._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsReplyContract.ReplyHistories.REPLY_STATUS
			+ " INTEGER ,"
			+ SmsReplyContract.ReplyHistories.PHONE_NUMBER
			+ " TEXT NOT NULL, "
			+ SmsReplyContract.ReplyHistories.NAME
			+ " TEXT, "
			+ SmsReplyContract.ReplyHistories.SOURCE_FROM
			+ " INTEGER NOT NULL DEFAULT "
			+ SmsReplyContract.ReplyHistories.REPLY_HISTORY_SOURCE_FROM_VALUE_PHONE
			+ ", "
			+ SmsReplyContract.ReplyHistories.REPLY_MESSAGE
			+ " TEXT, "
			+ SmsReplyContract.ReplyHistories.CREATION_DATE_TIME
			+ " INTEGER NOT NULL , "
			+ SmsReplyContract.ReplyHistories.REFERENCE_SMS_ACTIVATION_RECORDS_ID
			+ " INTEGER, "
			+ "FOREIGN KEY ("
			+ SmsReplyContract.ReplyHistories.REFERENCE_SMS_ACTIVATION_RECORDS_ID
			+ ") REFERENCES " + Tables.SMS.Reply.ACTIVATION_RECORDS + "("
			+ SmsReplyContract.ActivationRecords._ID + ") ON DELETE CASCADE"
			+ ")";

	// SmsReplyAppWidgetId
	private static final String SMS_REPLY_APPWIDGET_ID_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Reply.APP_WIDGET_IDS
			+ "("
			+ SmsReplyContract.SmsReplyAppWidgetIds._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsReplyContract.SmsReplyAppWidgetIds.APP_WIDGET_ID
			+ " INTEGER NOT NULL, "
			+ SmsReplyContract.SmsReplyAppWidgetIds.REFERENCE_SMS_REPLY_ID
			+ " INTEGER NOT NULL,"
			+ "FOREIGN KEY ("
			+ SmsReplyContract.SmsReplyAppWidgetIds.REFERENCE_SMS_REPLY_ID
			+ ") REFERENCES "
			+ Tables.SMS.Reply.REPLIES
			+ "("
			+ SmsReplyContract.SmsReplies._ID + ") ON DELETE CASCADE" + ")";

	// SMS Schedule Global Settings
	private static final String SMS_SCHEDULE_GLOBAL_SETTING_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Schedule.GLOBAL_SETTINGS
			+ "("
			+ SmsScheduleContract.SmsScheduleGlobalSettings._ID
			+ " INTEGER PRIMARY KEY, "
			+ SmsScheduleContract.SmsScheduleGlobalSettings.DO_NOT_SEND_SCHEDULE_MESSAGE_ON_ROAMING
			+ " INTEGER NOT NULL DEFAULT 0 " + ")";

	// SMS Schedule
	private static final String SMS_SCHEDULES_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Schedule.SCHEDULES + " ( "
			+ SmsScheduleContract.SmsSchedules._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsScheduleContract.SmsSchedules.SUBJECT + " TEXT, "
			+ SmsScheduleContract.SmsSchedules.SCHEDULE_DATE_TIME
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_TYPE
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_EVERY + " INTEGER, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_ON_SUNDAY
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_ON_MONDAY
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_ON_TUESDAY
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_ON_WEDNESDAY
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_ON_THURSDAY
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_ON_FRIDAY
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.REPEAT_ON_SATURDAY
			+ " INTEGER NOT NULL, " + SmsScheduleContract.SmsSchedules.MESSAGE
			+ " TEXT, " + SmsScheduleContract.SmsSchedules.IS_ACTIVATED
			+ " INTEGER NOT NULL, " + SmsScheduleContract.SmsSchedules.END_TYPE
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.END_AFTER_TIMES + " INTEGER, "
			+ SmsScheduleContract.SmsSchedules.END_ON_SPECIFIC_DATATIME
			+ " INTEGER, "
			+ SmsScheduleContract.SmsSchedules.NEXT_SCHEDULE_DATE_TIME
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.CREATION_DATE_TIME
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsSchedules.MODIFICATION_DATE_TIME
			+ " INTEGER NOT NULL " + " ) ";

	// SMS Schedule Recipients
	private static final String SMS_SCHEDULE_RECIPIENTS_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Schedule.RECIPIENTS
			+ " ( "
			+ SmsScheduleContract.SmsScheduleRecipients._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsScheduleContract.SmsScheduleRecipients.TYPE
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsScheduleRecipients.PHONE_NUMBER
			+ " TEXT NOT NULL, "
			+ SmsScheduleContract.SmsScheduleRecipients.NAME
			+ " TEXT, "
			+ SmsScheduleContract.SmsScheduleRecipients.GROUP_NAME
			+ " TEXT, "
			+ SmsScheduleContract.SmsScheduleRecipients.CREATION_DATE_TIME
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsScheduleRecipients.MODIFICATION_DATE_TIME
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsScheduleRecipients.REFERENCE_SMS_SCHEDULE_ID
			+ " INTEGER, "
			+ "FOREIGN KEY ("
			+ SmsScheduleContract.SmsScheduleRecipients.REFERENCE_SMS_SCHEDULE_ID
			+ ") REFERENCES "
			+ Tables.SMS.Schedule.SCHEDULES
			+ "("
			+ SmsScheduleContract.SmsSchedules._ID
			+ ") ON DELETE CASCADE"
			+ " ) "; // SMS Schedule Recipients
	private static final String SMS_SCHEDULE_SENDING_RECORDS_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Schedule.SENDING_RECORDS
			+ " ( "
			+ SmsScheduleContract.SmsScheduleSendingRecords._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsScheduleContract.SmsScheduleSendingRecords.MESSAGE
			+ " TEXT NOT NULL, "
			+ SmsScheduleContract.SmsScheduleSendingRecords.IS_VALID
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsScheduleSendingRecords.CREATION_DATE_TIME
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsScheduleSendingRecords.REFERENCE_SMS_SCHEDULE_ID
			+ " INTEGER, "
			+ "FOREIGN KEY ("
			+ SmsScheduleContract.SmsScheduleSendingRecords.REFERENCE_SMS_SCHEDULE_ID
			+ ") REFERENCES "
			+ Tables.SMS.Schedule.SCHEDULES
			+ "("
			+ SmsScheduleContract.SmsSchedules._ID
			+ ") ON DELETE SET NULL "
			+ " ) ";
	private static final String SMS_SCHEDULE_SENDING_RECORD_RECIPIENTS_CREATE_SCRIPT = "CREATE TABLE "
			+ Tables.SMS.Schedule.SENDING_RECORD_RECIPIENTS
			+ " ( "
			+ SmsScheduleContract.SmsScheduleSendingRecordRecipients._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SmsScheduleContract.SmsScheduleSendingRecordRecipients.CREATION_DATE_TIME
			+ " INTEGER NOT NULL, "
			+ SmsScheduleContract.SmsScheduleSendingRecordRecipients.PHONE_NUMBER
			+ " TEXT NOT NULL, "
			+ SmsScheduleContract.SmsScheduleSendingRecordRecipients.MESSAGE
			+ " TEXT NOT NULL, "
			+ SmsScheduleContract.SmsScheduleSendingRecordRecipients.SENDING_STATUS
			+ " INTEGER , "
			+ SmsScheduleContract.SmsScheduleSendingRecordRecipients.REFERENCE_SCHEDULE_SENDING_RECORD_ID
			+ " INTEGER, "
			+ "FOREIGN KEY ("
			+ SmsScheduleContract.SmsScheduleSendingRecordRecipients.REFERENCE_SCHEDULE_SENDING_RECORD_ID
			+ ") REFERENCES "
			+ Tables.SMS.Schedule.SENDING_RECORDS
			+ "("
			+ SmsScheduleContract.SmsScheduleSendingRecords._ID
			+ ") ON DELETE SET NULL " + " ) ";

	public PhoneUtilitySQLiteOpenHelper(Context context) {
		this(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	public PhoneUtilitySQLiteOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Enable foreign key
		configureSQLiteBaseSupportForeignKey(db);
		db.disableWriteAheadLogging();

		db.execSQL(PhoneUtilitySQLiteOpenHelper.PHONE_UTILITY_GLOBAL_SETTINGS_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.SMS_REPLY_GLOBAL_SETTING_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.SMS_REPLY_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.RESPONSE_NUMBER_FROM_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.NONE_REPLY_NUMBER_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.REPLY_TO_SPECIFIC_NUMBER_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.REPLY_TIMES_MODE_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.ACTIVATION_RECORD_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.REPLY_HISTORY_CREATE_SCRIPT);
		db.execSQL(PhoneUtilitySQLiteOpenHelper.SMS_REPLY_APPWIDGET_ID_CREATE_SCRIPT);

		ContentValues phoneUtilityGlobalSettingContentValues = new ContentValues();
		phoneUtilityGlobalSettingContentValues.put(
				PhoneUtilityContract.PhoneUtilityGlobalSettings._ID, 1);
		db.insert(Tables.GLOBAL_SETTINGS, null,
				phoneUtilityGlobalSettingContentValues);

		ContentValues replyGlobalSettingContentValues = new ContentValues();
		replyGlobalSettingContentValues.put(
				SmsReplyContract.SmsReplyGlobalSettings._ID, 1);
		replyGlobalSettingContentValues
				.put(SmsReplyContract.SmsReplyGlobalSettings.ENABLE_AVOID_EXCESSIVE_REPLY,
						1);
		replyGlobalSettingContentValues
				.put(SmsReplyContract.SmsReplyGlobalSettings.SHOULD_NOTIFICATION_DISPLAY_IN_NOTIFICATION_BAR,
						1);
		replyGlobalSettingContentValues
				.put(SmsReplyContract.SmsReplyGlobalSettings.DO_NOT_SEND_REPLY_MESSAGE_ON_ROAMING,
						0);
		db.insert(Tables.SMS.Reply.GLOBAL_SETTINGS, null,
				replyGlobalSettingContentValues);

		db.execSQL(SMS_SCHEDULE_GLOBAL_SETTING_CREATE_SCRIPT);
		db.execSQL(SMS_SCHEDULES_CREATE_SCRIPT);
		db.execSQL(SMS_SCHEDULE_RECIPIENTS_CREATE_SCRIPT);
		db.execSQL(SMS_SCHEDULE_SENDING_RECORDS_CREATE_SCRIPT);
		db.execSQL(SMS_SCHEDULE_SENDING_RECORD_RECIPIENTS_CREATE_SCRIPT);

		initSmsScheduleGlobalSettings(db);
	}

	private PhoneUtilitySQLiteOpenHelper(Context context, String name,
			CursorFactory factory, int version,
			DatabaseErrorHandler errorHandler) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION, errorHandler);
	}

	@Override
	public void onConfigure(SQLiteDatabase db) {
		super.onConfigure(db);
		configureSQLiteBaseSupportForeignKey(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i(LOG_TAG, "Upgrading DB from version " + oldVersion + " to "
				+ newVersion);
		if (oldVersion == 1) {
			upgradeTo2(db);
			oldVersion = 2;
			db.setVersion(oldVersion);
		}

		if (oldVersion == 2) {
			upgradeTo3(db);
			oldVersion = 3;
			db.setVersion(oldVersion);
		}

		if (oldVersion == 3) {
			upgradeTo4(db);
			oldVersion = 4;
			db.setVersion(oldVersion);
		}
	}

	private void configureSQLiteBaseSupportForeignKey(SQLiteDatabase db) {
		if (db.isReadOnly() == false) {
			db.setForeignKeyConstraintsEnabled(true);
		}
	}

	private void upgradeTo2(SQLiteDatabase db) {
		db.execSQL("ALTER TABLE "
				+ Tables.SMS.Reply.REPLIES
				+ " ADD COLUMN "
				+ SmsReplyContract.SmsReplies.ENABLE_NOT_RESPONSE_TO_RESPONSE_CALL
				+ " INTEGER DEFAULT 0 NOT NULL");
		db.execSQL("UPDATE  "
				+ Tables.SMS.Reply.REPLIES
				+ " SET "
				+ SmsReplyContract.SmsReplies.ENABLE_NOT_RESPONSE_TO_RESPONSE_CALL
				+ " = 0 ");
		db.execSQL("ALTER TABLE "
				+ Tables.SMS.Reply.REPLIES
				+ " ADD COLUMN "
				+ SmsReplyContract.SmsReplies.DELAY_TIME_FOR_NOT_RESPONSE_TO_RESPONSE_CALL
				+ " INTEGER ");
	}

	private void upgradeTo3(SQLiteDatabase db) {
		db.execSQL("ALTER TABLE " + Tables.SMS.Reply.REPLIES + " ADD COLUMN "
				+ SmsReplyContract.SmsReplies.ENABLE_RINGER_MODE_SILENT
				+ " INTEGER DEFAULT 0 NOT NULL");
		db.execSQL("UPDATE  " + Tables.SMS.Reply.REPLIES + " SET "
				+ SmsReplyContract.SmsReplies.ENABLE_RINGER_MODE_SILENT
				+ " = 0 ");
		db.execSQL("UPDATE  " + Tables.SMS.Schedule.SCHEDULES + " SET "
				+ SmsScheduleContract.SmsSchedules.MESSAGE + " = "
				+ " REPLACE(" + SmsScheduleContract.SmsSchedules.MESSAGE + ", "
				+ "'[<GIVEN_NAME>]', " + "'[<FIRST_NAME>]'" + ")" + " WHERE "
				+ SmsScheduleContract.SmsSchedules.MESSAGE
				+ " LIKE '%[<GIVEN_NAME>]%'");
	}

	private void upgradeTo4(SQLiteDatabase db) {
		db.execSQL("DROP TABLE phone_utility_enviroment_setting");
		db.execSQL(PHONE_UTILITY_GLOBAL_SETTINGS_CREATE_SCRIPT);

		db.execSQL("ALTER TABLE "
				+ Tables.SMS.Reply.GLOBAL_SETTINGS
				+ " ADD COLUMN "
				+ SmsReplyContract.SmsReplyGlobalSettings.DO_NOT_SEND_REPLY_MESSAGE_ON_ROAMING
				+ " INTEGER DEFAULT 0 NOT NULL");

		db.execSQL(SMS_SCHEDULE_GLOBAL_SETTING_CREATE_SCRIPT);
		initSmsScheduleGlobalSettings(db);
	}

	private void initSmsScheduleGlobalSettings(SQLiteDatabase db) {
		ContentValues smsScheduleContentValues = new ContentValues();
		smsScheduleContentValues.put(
				SmsScheduleContract.SmsScheduleGlobalSettings._ID, 1l);
		smsScheduleContentValues
				.put(SmsScheduleContract.SmsScheduleGlobalSettings.DO_NOT_SEND_SCHEDULE_MESSAGE_ON_ROAMING,
						0);
		db.insert(Tables.SMS.Schedule.GLOBAL_SETTINGS, null,
				smsScheduleContentValues);
	}

}
